﻿CREATE PROCEDURE [dbo].[CarReport]

AS
WITH myTable AS
(
  SELECT *, rn = ROW_NUMBER() OVER (ORDER BY carNumber,Month(operationTime),DAY(operationTime),DatePart(HOUR, operationTime),DatePart(MINUTE, operationTime))
  FROM SomeTable
)

SELECT myTable.carNumber as carNumber, upload.operationTime as upload,download.operationTime as download,departute.operationTime as departure,destination.operationTime as arrive FROM myTable 
LEFT OUTER JOIN myTable AS upload
	ON myTable.rn = upload.rn + 1
	AND myTable.cargoWeight > upload.cargoWeight AND myTable.carNumber = upload.carNumber
LEFT OUTER JOIN myTable as download
	ON myTable.rn = download.rn + 1
	AND myTable.cargoWeight < download.cargoWeight AND myTable.carNumber = download.carNumber
	
LEFT OUTER JOIN myTable as departute
	ON myTable.rn + 1 = departute.rn
	AND myTable.destinationStation <> departute.destinationStation AND myTable.carNumber = departute.carNumber AND departute.operationTime IN(SELECT MIN(operationTime) from myTable where departute.destinationStation = myTable.destinationStation AND myTable.carNumber = departute.carNumber)
	
LEFT OUTER JOIN myTable as destination
	on myTable.rn +1  = destination.rn 
	and myTable.carNumber = destination.carNumber AND myTable.destinationStation = destination.stationOfOperation
	WHERE ((upload.cargoWeight IS NOT NULL OR download.cargoWeight IS NOT NULL))
	ORDER BY myTable.carNumber
